package com.util;

import java.sql.*;
import java.util.Random;

public class DatabaseUtil {

    private static final String[] CN_CITY = {"CN_Beijing", "CN_Shanghai", "CN_Guangzhou", "CN_Shenzhen"};
    private static final String[] AU_CITY = {"AU_Melbourne", "AU_Sydney", "AU_Canberra", "AU_Adelaide"};
    private static final String[] CN_AIRPORT = {"Capital_Airport", "Pudong_Airport", "Baiyun_Airport", "Baoshan_Airport"};
    private static final String[] AU_AIRPORT = {"Tullamarine_Airport", "KingsfordSmith_Airport", "Canberra_Airport", "Adelaide_Airport"};
    private static final String[] SEATCLASS = {"ECO", "BUS", "FIR"};
    Connection connection = null;
    public DatabaseUtil() {
        connect();
    }

    /**
     * @param args
     */
    public static void main(String[] args) {
        // TODO Auto-generated method stub
        DatabaseUtil databaseUtil = new DatabaseUtil();
        databaseUtil.insertToAirlineTables("airline3", 6);
        databaseUtil.insertFlight(3);
    }

    public void connect() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(Constant.CONNECTION_STRING, Constant.USERNAME_STRING, "");
        } catch (Exception e) {
            System.out.println("Fail to build connection!");
        }
    }

    public void insertToAirlineTables(String airline, int count) {
        if (connection == null) {
            return;
        } else {
            String day = "";
            String hour = "";
            String minute = "";
            int[] price = new int[3];
            int[] seat = new int[3];
            int city = 0;
            int city2 = 0;
            switch (airline) {
                case "airline1":
                    String sqlString1 = "INSERT INTO airline1 VALUES ( ?, 'Air China', ?, ?, ?, ?, ?, ?, 'Air Bus 321', ?, ? )";

                    try {
                        PreparedStatement preparedStatement = connection.prepareStatement(sqlString1);
                        for (int i = 0; i < count; i++) {
                            hour = hour();
                            minute = minute();
                            price = price();
                            city = new Random().nextInt(4);
                            preparedStatement.setString(1, "CA" + hour + minute);
                            preparedStatement.setString(2, CN_CITY[city]);
                            preparedStatement.setString(3, AU_CITY[city]);
//                            preparedStatement.setString(4, "2014-03-" + day);
                            if (Integer.parseInt(hour) < 14) {
//                                preparedStatement.setString(5, "2014-03-" + day);
                                preparedStatement.setString(7, Integer.parseInt(hour) + 10 + ":" + minute + ":00");
                            } else {
//                                preparedStatement.setString(5, "2014-03-" + (Integer.parseInt(day) + 1));
                                preparedStatement.setString(7, Integer.parseInt(hour) - 14 + ":" + minute + ":00");
                            }
                            preparedStatement.setString(4, CN_AIRPORT[city]);
                            preparedStatement.setString(5, AU_AIRPORT[city]);
                            preparedStatement.setString(6, hour + ":" + minute + ":00");
                            preparedStatement.setString(8, SEATCLASS[0]);
                            preparedStatement.setInt(9, price[0]);

                            preparedStatement.executeUpdate();

                            preparedStatement.setString(8, SEATCLASS[1]);
                            preparedStatement.setInt(9, price[1]);

                            preparedStatement.executeUpdate();

                            preparedStatement.setString(8, SEATCLASS[2]);
                            preparedStatement.setInt(9, price[2]);

                            preparedStatement.executeUpdate();

                            // ====================================================================

                            hour = hour();
                            minute = minute();
                            price = price();
                            city = new Random().nextInt(4);
                            preparedStatement.setString(1, "AC" + hour + minute);
                            preparedStatement.setString(2, AU_CITY[city]);
                            preparedStatement.setString(3, CN_CITY[city]);
//                            preparedStatement.setString(4, "2014-03-" + day);
                            if (Integer.parseInt(hour) < 14) {
//                                preparedStatement.setString(5, "2014-03-" + day);
                                preparedStatement.setString(7, Integer.parseInt(hour) + 10 + ":" + minute + ":00");
                            } else {
//                                preparedStatement.setString(5, "2014-03-" + (Integer.parseInt(day) + 1));
                                preparedStatement.setString(7, Integer.parseInt(hour) - 14 + ":" + minute + ":00");
                            }
                            preparedStatement.setString(4, AU_AIRPORT[city]);
                            preparedStatement.setString(5, CN_AIRPORT[city]);
                            preparedStatement.setString(6, hour + ":" + minute + ":00");
                            preparedStatement.setString(8, SEATCLASS[0]);
                            preparedStatement.setInt(9, price[0]);

                            preparedStatement.executeUpdate();

                            preparedStatement.setString(8, SEATCLASS[1]);
                            preparedStatement.setInt(9, price[1]);

                            preparedStatement.executeUpdate();

                            preparedStatement.setString(8, SEATCLASS[2]);
                            preparedStatement.setInt(9, price[2]);

                            preparedStatement.executeUpdate();
                        }
                    } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    }
                    break;
                case "airline2":
                    String sqlString2 = "INSERT INTO airline2 VALUES ( ?, 'Air China', ?, ?, ?, ?, ?, ?, 'Air Bus 321', ?, ? )";

                    try {
                        PreparedStatement preparedStatement = connection.prepareStatement(sqlString2);
                        for (int i = 0; i < count; i++) {
                            hour = hour();
                            minute = minute();
                            price = price();
                            city = new Random().nextInt(4);
                            preparedStatement.setString(1, "CA" + hour + minute);
                            preparedStatement.setString(2, CN_CITY[city]);
                            preparedStatement.setString(3, AU_CITY[city]);
//                            preparedStatement.setString(4, "2014-03-" + day);
                            if (Integer.parseInt(hour) < 14) {
//                                preparedStatement.setString(5, "2014-03-" + day);
                                preparedStatement.setString(7, Integer.parseInt(hour) + 10 + ":" + minute + ":00");
                            } else {
//                                preparedStatement.setString(5, "2014-03-" + (Integer.parseInt(day) + 1));
                                preparedStatement.setString(7, Integer.parseInt(hour) - 14 + ":" + minute + ":00");
                            }
                            preparedStatement.setString(4, CN_AIRPORT[city]);
                            preparedStatement.setString(5, AU_AIRPORT[city]);
                            preparedStatement.setString(6, hour + ":" + minute + ":00");
                            preparedStatement.setString(8, SEATCLASS[0]);
                            preparedStatement.setInt(9, price[0]);

                            preparedStatement.executeUpdate();

                            preparedStatement.setString(8, SEATCLASS[1]);
                            preparedStatement.setInt(9, price[1]);

                            preparedStatement.executeUpdate();

                            preparedStatement.setString(8, SEATCLASS[2]);
                            preparedStatement.setInt(9, price[2]);

                            preparedStatement.executeUpdate();

                            // ====================================================================

                            hour = hour();
                            minute = minute();
                            price = price();
                            city = new Random().nextInt(4);
                            preparedStatement.setString(1, "AC" + hour + minute);
                            preparedStatement.setString(2, AU_CITY[city]);
                            preparedStatement.setString(3, CN_CITY[city]);
//                            preparedStatement.setString(4, "2014-03-" + day);
                            if (Integer.parseInt(hour) < 14) {
//                                preparedStatement.setString(5, "2014-03-" + day);
                                preparedStatement.setString(7, Integer.parseInt(hour) + 10 + ":" + minute + ":00");
                            } else {
//                                preparedStatement.setString(5, "2014-03-" + (Integer.parseInt(day) + 1));
                                preparedStatement.setString(7, Integer.parseInt(hour) - 14 + ":" + minute + ":00");
                            }
                            preparedStatement.setString(4, AU_AIRPORT[city]);
                            preparedStatement.setString(5, CN_AIRPORT[city]);
                            preparedStatement.setString(6, hour + ":" + minute + ":00");
                            preparedStatement.setString(8, SEATCLASS[0]);
                            preparedStatement.setInt(9, price[0]);

                            preparedStatement.executeUpdate();

                            preparedStatement.setString(8, SEATCLASS[1]);
                            preparedStatement.setInt(9, price[1]);

                            preparedStatement.executeUpdate();

                            preparedStatement.setString(8, SEATCLASS[2]);
                            preparedStatement.setInt(9, price[2]);

                            preparedStatement.executeUpdate();
                        }
                    } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    }
                    break;
                case "airline3":
                    String sqlString3 = "INSERT INTO airline3 VALUES ( ?, 'Air China', ?, ?, ?, ?, ?, ?, 'Boeing 747', ?, ? )";

                    try {
                        PreparedStatement preparedStatement = connection.prepareStatement(sqlString3);
                        for (int i = 0; i < count; i++) {
                            hour = hour();
                            minute = minute();
                            price = price();
                            city = new Random().nextInt(4);
                            preparedStatement.setString(1, "CA" + hour + minute);
                            preparedStatement.setString(2, CN_CITY[city]);
                            preparedStatement.setString(3, AU_CITY[city]);
//                            preparedStatement.setString(4, "2014-03-" + day);
                            if (Integer.parseInt(hour) < 14) {
//                                preparedStatement.setString(5, "2014-03-" + day);
                                preparedStatement.setString(7, Integer.parseInt(hour) + 10 + ":" + minute + ":00");
                            } else {
//                                preparedStatement.setString(5, "2014-03-" + (Integer.parseInt(day) + 1));
                                preparedStatement.setString(7, Integer.parseInt(hour) - 14 + ":" + minute + ":00");
                            }
                            preparedStatement.setString(4, CN_AIRPORT[city]);
                            preparedStatement.setString(5, AU_AIRPORT[city]);
                            preparedStatement.setString(6, hour + ":" + minute + ":00");
                            preparedStatement.setString(8, SEATCLASS[0]);
                            preparedStatement.setInt(9, price[0]);

                            preparedStatement.executeUpdate();

                            preparedStatement.setString(8, SEATCLASS[1]);
                            preparedStatement.setInt(9, price[1]);

                            preparedStatement.executeUpdate();

                            preparedStatement.setString(8, SEATCLASS[2]);
                            preparedStatement.setInt(9, price[2]);

                            preparedStatement.executeUpdate();

                            // ====================================================================

                            hour = hour();
                            minute = minute();
                            price = price();
                            city = new Random().nextInt(4);
                            preparedStatement.setString(1, "AC" + hour + minute);
                            preparedStatement.setString(2, AU_CITY[city]);
                            preparedStatement.setString(3, CN_CITY[city]);
//                            preparedStatement.setString(4, "2014-03-" + day);
                            if (Integer.parseInt(hour) < 14) {
//                                preparedStatement.setString(5, "2014-03-" + day);
                                preparedStatement.setString(7, Integer.parseInt(hour) + 10 + ":" + minute + ":00");
                            } else {
//                                preparedStatement.setString(5, "2014-03-" + (Integer.parseInt(day) + 1));
                                preparedStatement.setString(7, Integer.parseInt(hour) - 14 + ":" + minute + ":00");
                            }
                            preparedStatement.setString(4, AU_AIRPORT[city]);
                            preparedStatement.setString(5, CN_AIRPORT[city]);
                            preparedStatement.setString(6, hour + ":" + minute + ":00");
                            preparedStatement.setString(8, SEATCLASS[0]);
                            preparedStatement.setInt(9, price[0]);

                            preparedStatement.executeUpdate();

                            preparedStatement.setString(8, SEATCLASS[1]);
                            preparedStatement.setInt(9, price[1]);

                            preparedStatement.executeUpdate();

                            preparedStatement.setString(8, SEATCLASS[2]);
                            preparedStatement.setInt(9, price[2]);

                            preparedStatement.executeUpdate();
                        }
                    } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    }
                    break;
                default:
                    break;
            }

        }
    }

    String day() {
        int i = new Random().nextInt(30) + 1;
        if (i < 10) {
            return "0" + i;
        } else {
            return i + "";
        }
    }

    String hour() {
        int i = new Random().nextInt(23) + 1;
        if (i < 10) {
            return "0" + i;
        } else {
            return i + "";
        }
    }

    String minute() {
        int i = new Random().nextInt(59) + 1;
        if (i < 10) {
            return "0" + i;
        } else {
            return i + "";
        }
    }

    int[] price() {
        int[] ints = new int[3];
        for (int t = 0; t < 3; t++) {
            int i = new Random().nextInt(2000) + 3000 * (t + 1);
            ints[t] = i;
        }
        return ints;
    }

    int[] seat() {
        int[] ints = new int[3];
        int i = new Random().nextInt(15) + 1;
        ints[2] = i;
        ints[1] = i;
        i = new Random().nextInt(150) + 1;
        ints[0] = i;
        return ints;
    }

    public void insertFlight(int airline) {

        String sqlString1 = "SELECT DISTINCT FLNO FROM airline" + airline;
        String sqlString2 = "INSERT INTO flight" + airline + " VALUES ( ?, ?, ?, ?, ?)";

        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sqlString1);
            ResultSet resultSet1 = preparedStatement.executeQuery();

            if (!resultSet1.next()) {
//                Printer.p(resultSet1.getString(1));
                return;
            }

            resultSet1.last();
            int rows = resultSet1.getRow();
            resultSet1.first();
            preparedStatement = connection.prepareStatement(sqlString2);

            for (int i = 0; i < rows; i++) {

                String day = day();
                int[] seat = seat();
                String hour = resultSet1.getString("FLNO").substring(2,4);
                Printer.p(hour);
                preparedStatement.setString(2, "2014-03-" + day);

                preparedStatement.setString(1,resultSet1.getString("FLNO"));
                if (Integer.parseInt(hour) > 14) {
                    preparedStatement.setString(3, "2014-03-" + (Integer.parseInt(day) + 1));
                } else {
                    preparedStatement.setString(3, "2014-03-" + day);
                }
                preparedStatement.setString(4,SEATCLASS[0]);
                preparedStatement.setInt(5,seat[0]);

                preparedStatement.executeUpdate();

                if (Integer.parseInt(hour) > 14) {
                    preparedStatement.setString(3, "2014-03-" + (Integer.parseInt(day) + 1));
                } else {
                    preparedStatement.setString(3, "2014-03-" + day);
                }
                preparedStatement.setString(4,SEATCLASS[1]);
                preparedStatement.setInt(5,seat[1]);

                preparedStatement.executeUpdate();

                if (Integer.parseInt(hour) > 14) {
                    preparedStatement.setString(3, "2014-03-" + (Integer.parseInt(day) + 1));
                } else {
                    preparedStatement.setString(3, "2014-03-" + day);
                }
                preparedStatement.setString(4,SEATCLASS[2]);
                preparedStatement.setInt(5,seat[2]);

                preparedStatement.executeUpdate();

                resultSet1.next();
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}
